# -*- coding: UTF-8 -*-

import datetime
import sys
from sqlalchemy import and_,  func, or_, desc
from typing import Dict, List, Any, Generator

from sqlalchemy import Boolean, Column, DateTime, Integer, String, Index, Table, ForeignKey, text, JSON, TEXT, DECIMAL, FLOAT
from sqlalchemy.orm import Session, relationship
from common.database import Base
from common.database import SessionMixin
from common.utils import is_empty
from core.logger import logger


__all__ = ['BillAnalysis']

class BillAnalysis(Base, SessionMixin):
    '''
    账单分析对比数据
    '''
    __tablename__ = "bill_analysis"
    bill_cycle = Column(String(64), nullable=False, comment='账期')
    cloud_platform = Column(String(64), comment='云平台')
    company = Column(String(128), nullable=False, comment='公司')
    bill_class = Column(String(128), nullable=False, index=True, comment='资产归类')
    environment = Column(String(128), nullable=False, index=True, comment='环境')
    maintain_department = Column(String(128), nullable=False, index=True, comment='维护部门')
    belong_department = Column(String(128), nullable=False, index=True, comment='归属部门')
    tag_key = Column(String(256), nullable=True, comment='标签名字')
    tag_value = Column(String(256), nullable=True, comment='标签值')
    analysis_class = Column(String(64), nullable=False, comment='分析账单种类') #,增加/减少
    analysis_type = Column(String(64), nullable=False, comment='分析账单类型') #,4种类型:新增/升级/将配/回收
    bill_item = Column(String(128), nullable=False, comment='账单计费项')
    account_name = Column(String(128), nullable=False, comment='账号名称')
    product_name = Column(String(128), nullable=False, comment='产品名称')
    instance_id = Column(String(128), nullable=False, comment='实例ID')
    instance_name = Column(String(256), nullable=False, comment='实例名称')
    pretax_amount = Column(DECIMAL(10,2), nullable=False, comment='应付金额')
    differ_amount = Column(DECIMAL(10,2), nullable=False, comment='差额')
    events = Column(JSON, nullable=False, default=[], comment='事件信息')
    comment = Column(String(256), nullable=True, comment='分析说明')

    __table_args__ = (
        Index('idx_bill_analysis_bill_cycle', 'bill_cycle', unique=False),
        Index('idx_bill_analysis_analysis_class', 'analysis_class', unique=False),
        Index('idx_bill_analysis_analysis_type', 'analysis_type', unique=False),
        {
            'mysql_engine': 'innodb',
            'mysql_charset': 'utf8mb4',
            'mysql_collate': 'utf8mb4_unicode_ci',
            'extend_existing': True,
            'mysql_row_format': 'dynamic'
        }
    )

    def __repr__(self):
        return f"<pid='{self.bill_cycle}', {self.product_name}[{self.instance_id}], {self.instance_name}='{self.pretax_amount}'>"


    @classmethod
    def delete_cycle(cls, db: Session, bill_cycle: str):
        db.query(cls).filter(
            cls.bill_cycle == bill_cycle
        ).delete()
        db.commit()



    @classmethod
    def get_bills_for_belong_department(cls, db: Session, bill_cycle: str, belong_department: str):
        """
        获取某一个资产所属部门的差异账单信息
        """
        result = db.query(cls).filter(and_(
            cls.bill_cycle == bill_cycle,
            cls.belong_department == belong_department)
        ).to_dict()

        return result




    @classmethod
    def get_group_by_company(cls, db: Session, bill_cycle: str):
        """
        获取公司名称，聚合数据
        """
        result = db.query(cls).filter(
            cls.bill_cycle == bill_cycle
        ).with_entities(
            cls.company,
            func.round(func.sum(BillAnalysis.pretax_amount), 2).label('pretax_total'),
            func.round(func.sum(BillAnalysis.differ_amount), 2).label('differ_total'),
        ).group_by(
            cls.company
        ).to_dict()

        return result


    @classmethod
    def get_group_by_analysis_class(cls, db: Session, bill_cycle: str, company: str):
        """
        根据分析账单种类（增加/减少）,聚合数据
        """
        result = db.query(cls).filter(and_(
            cls.bill_cycle == bill_cycle,
            cls.company == company,)
        ).with_entities(
            cls.analysis_class,
            func.round(func.sum(BillAnalysis.pretax_amount), 2).label('pretax_total'),
            func.round(func.sum(BillAnalysis.differ_amount), 2).label('differ_total'),
        ).group_by(
            cls.analysis_class
        ).to_dict()

        return result



    @classmethod
    def get_group_by_department(cls, db: Session, bill_cycle: str, company: str, analysis_class: str):
        """
        获取资产所管部门，聚合数据
        """
        result = db.query(cls).filter(and_(
            cls.bill_cycle == bill_cycle,
            cls.company == company,
            cls.analysis_class == analysis_class,)
        ).with_entities(
            cls.maintain_department,
            func.round(func.sum(BillAnalysis.pretax_amount), 2).label('pretax_total'),
            func.round(func.sum(BillAnalysis.differ_amount), 2).label('differ_total'),
        ).group_by(
            cls.maintain_department
        ).to_dict()

        return result


    @classmethod
    def get_group_by_belong_department(cls, db: Session, bill_cycle: str, company: str, analysis_class: str):
        """
        根据资产所属部门，聚合数据
        """
        order = desc("differ_total") if analysis_class == "增加" else "differ_total"

        result = db.query(cls).filter(and_(
            cls.bill_cycle == bill_cycle,
            cls.company == company,
            cls.analysis_class == analysis_class)
        ).with_entities(
            cls.belong_department,
            func.round(func.sum(BillAnalysis.pretax_amount), 2).label('pretax_total'),
            func.round(func.sum(BillAnalysis.differ_amount), 2).label('differ_total'),
        ).group_by(
            cls.belong_department
        ).order_by(order).limit(5).to_dict()

        return result


    @classmethod
    def get_group_by_product_name(cls, db: Session, bill_cycle: str, company: str, analysis_class: str, belong_department: str):
        """
        根据产品名称，聚合数据
        """
        result = db.query(cls).filter(and_(
            cls.bill_cycle == bill_cycle,
            cls.company == company,
            cls.analysis_class == analysis_class,
            cls.belong_department == belong_department)
        ).with_entities(
            cls.product_name,
            func.round(func.sum(BillAnalysis.pretax_amount), 2).label('pretax_total'),
            func.round(func.sum(BillAnalysis.differ_amount), 2).label('differ_total'),
        ).group_by(
            cls.product_name
        ).to_dict()

        return result



    # 账期内, 分析账单表, 根据field字段模糊查询value
    @classmethod
    def get_analysis_search(cls, db: Session, bill_cycle: str, page: int, per_page: int, field: str, value: str):
        try:
            search_field = []
            display_field = cls.get_display_field()
            if value or field != "all":
                for key, col in cls.__table__.c.items():
                    if field == "all" or key == field:
                        search_field.append(col.like(f"%{value}%"))

            result = db.query(cls).filter(
                cls.bill_cycle == bill_cycle,
                or_(*search_field)
            ).with_entities(
                *display_field
            ).paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        # 每个字段的说明
        result["fields"] = cls.get_display_field_meta()

        return result



    # 账期内, 分析账单表, 根据field字段完全匹配value
    @classmethod
    def get_analysis_match(cls, db: Session, bill_cycle: str, page: int, per_page: int, field: str, value: str):
        try:
            search_field = []
            display_field = cls.get_display_field()
            for key, col in cls.__table__.c.items():
                if key == field:
                    search_field.append(col == value)
                    break
            else:
                logger.error(f'field:{field} not in table')
                return None

            result = db.query(cls).filter(
                cls.bill_cycle == bill_cycle,
                *search_field
            ).with_entities(
                *display_field
            ).paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        # 每个字段的说明
        result["fields"] = cls.get_display_field_meta()

        return result



    # 账期内, 分析账单表, 根据field字段聚合
    @classmethod
    def get_analysis_aggregate(cls, db: Session, bill_cycle: str, field: str, page: int, per_page: int):
        try:
            display_field_meta = []
            display_field = [cls.bill_cycle]
            for key, col in cls.__table__.c.items():
                comment = col.comment.split(' ')[0]
                if key == field:
                    group_by_field = col
                    display_field.append(col)
                    display_field_meta.append({
                        "field": key,
                        "comment": comment
                    })
                    break
            else:
                logger.error(f'field:{field} not in table')
                return None

            result = db.query(cls).filter(
                cls.bill_cycle == bill_cycle
            ).with_entities(
                *display_field,
                func.round(func.sum(cls.pretax_amount), 2).label('pretax_amount'),
                func.round(func.sum(cls.differ_amount), 2).label('differ_amount')
            ).group_by(group_by_field).order_by("differ_amount").paginate(page=page, per_page=per_page)
        except Exception as e:
            logger.exception(f'{sys._getframe().f_code.co_name} {e}')
            return None

        result["total_amount"] = 0
        for item in result["items"]:
            result["total_amount"] =  round(result["total_amount"] + item["differ_amount"], 2)


        # 每个字段的说明
        result["fields"] = display_field_meta.extend([
                {
                    "field": "bill_cycle",
                    "comment": "账期"
                },
                {
                    "field": "pretax_amount",
                    "comment": "金额"
                },
                {
                    "field": "differ_amount",
                    "comment": "差额"
                }
            ])

        return result